# -*- utf-8 -*-
import pandas as pd
from sqlalchemy import select, func, delete

from apps.data_visual.sqla_models import get_engine, ShopInfo


def tasks():
    sql = select(ShopInfo.id).select_from(ShopInfo).where(ShopInfo.id.not_in(
        select(func.max(ShopInfo.id)).select_from(ShopInfo).where(
            func.date_format(ShopInfo.create_time, '%Y%m%d') == func.date_format(func.now(), '%Y%m%d')
        ).group_by(ShopInfo.title)
    )).where(
        func.date_format(ShopInfo.create_time, '%Y%m%d') == func.date_format(func.now(), '%Y%m%d')
    )
    engine = get_engine()
    df = pd.read_sql(sql, engine)
    ids = list(df['id'])
    d_sql = delete(ShopInfo).where(ShopInfo.id.in_(ids))
    with engine.connect() as conn:
        conn.execute(d_sql)
